package com.wosai.register.repository.jdbc;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.lang.exception.ExceptionUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;

import com.wosai.register.model.Order;
import com.wosai.register.repository.OrderRepository;
import com.wosai.register.util.ExceptionUtil;
import com.wosai.register.util.StringUtil;

@Repository
public class JdbcOrderRepository implements OrderRepository {
    private final static Log logger = LogFactory.getLog(JdbcOrderRepository.class);

    private NamedParameterJdbcTemplate npjt;
    private SimpleJdbcInsert sji;

    private final static String SELECT_BY_ID = "select * from ORDER2 where merchant_id = :merchantId and terminal_id = :terminalId and branch_id=:branchId and id=:id";
    private final static String SELECT_BY_TERMINAL = "select * from ORDER2 " +
    		                                         "where merchant_id = :merchantId and branch_id=:branchId and terminal_id = :terminalId and " +
    		                                         "date_paid >= :startDate and date_paid < :endDate " +
    		                                         "order by id desc limit :offset, :size";

    private final static String DELETE = "delete from ORDER2 where merchant_id = :merchantId and terminal_id = :terminalId and id = :id";
    private final static String DELETE_BY_TERMINAL = "delete from ORDER2 where merchant_id = :merchantId and terminal_id = :terminalId";
    
    private final static String UPDATE = "update ORDER2 set type=:type, total=:total, date_paid=:datePaid, state=:state, " +
    		                             "discount=:discount, date_returned=:dateReturned, extra_discount_type=:extraDiscountType, " +
    		                             "extra_discount_value=:extraDiscountValue, operator_user_id=:operatorUserId, " +
    		                             "operator_username=:operatorUsername, " +
    		                             "orderid_hist=:orderidHist, order_bak1=:orderBak1, order_bak2=:orderBak2, order_bak3=:orderBak3 " +
    		                             "where merchant_id = :merchantId and terminal_id = :terminalId and id=:id ";
    
    @Autowired
    public JdbcOrderRepository(DataSource dataSource) {
        this.sji = new SimpleJdbcInsert(dataSource)
                            .withTableName("ORDER2");

        this.npjt = new NamedParameterJdbcTemplate(dataSource);
    }

    @Override
    public Order findById(String merchantId, String branchId, String terminalId, String id) {
        
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("merchantId", merchantId);
        params.put("branchId", branchId);
        params.put("terminalId", terminalId);
        params.put("id", id);
        
        return this.npjt.queryForObject(SELECT_BY_ID, params, ParameterizedBeanPropertyRowMapper.newInstance(Order.class));
    }

    @Override
    public List<Order> findByTerminal(String merchantId, String branchId, String terminalId,
                                      int offset, int size,
                                      String startDate, String endDate)
    {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("merchantId", merchantId);
        params.put("branchId", branchId);
        params.put("terminalId", terminalId);
        params.put("offset", offset);
        params.put("size", size);
        params.put("startDate", startDate);
        params.put("endDate", endDate);
        
        return this.npjt.query(SELECT_BY_TERMINAL, params, ParameterizedBeanPropertyRowMapper.newInstance(Order.class));
    }


    @Override
    public List<String> insertBatch(List<Order> orders) {
        SqlParameterSource[] batch = new SqlParameterSource[orders.size()];
        for(int i=0; i<batch.length; ++i)
            batch[i] = new BeanPropertySqlParameterSource(orders.get(i));
        
        ArrayList<String> failed = new ArrayList<String>();
        try{
            int[] rows = this.sji.executeBatch(batch);
            logger.info("batch inserted " + StringUtil.intArrayToString(rows));
        }catch(DataAccessException dae){
            List<Integer> failedIndices = ExceptionUtil.extractFailedStatements(dae);
            for(int i: failedIndices){
                failed.add(orders.get(i).getId());
            }
            logger.warn("error in batch insert " + failedIndices);
            logger.warn("detailed error info " + ExceptionUtils.getStackTrace(dae));
        }

        return failed;

    }

    @Override
    public List<String> updateBatch(List<Order> orders) {
        SqlParameterSource[] batch = new SqlParameterSource[orders.size()];
        for(int i=0; i<batch.length; ++i)
            batch[i] = new BeanPropertySqlParameterSource(orders.get(i));
        
        ArrayList<String> failed = new ArrayList<String>();
        try{
            int[] rows = this.npjt.batchUpdate(UPDATE, batch);
            logger.info("batch updated " + StringUtil.intArrayToString(rows));
        }catch(DataAccessException dae){
            List<Integer> failedIndices = ExceptionUtil.extractFailedStatements(dae);
            for(int i: failedIndices){
                failed.add(orders.get(i).getId());
            }
            logger.warn("error in batch update " + failedIndices);
            logger.warn("detailed error info " + ExceptionUtils.getStackTrace(dae));

        }

        return failed;
        
    }

    @Override
    public void delete(String merchantId, String branchId, String terminalId, String id) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("merchantId", merchantId);
        params.put("branchId", branchId);
        params.put("terminalId", terminalId);
        params.put("id", id);
        
        int rows = this.npjt.update(DELETE, params);
        logger.info("deleted " + rows);
    }

    @Override
    public void deleteByTerminal(String merchantId, String branchId, String terminalId) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("merchantId", merchantId);
        params.put("branchId", branchId);
        params.put("terminalId", terminalId);
        
        int rows = this.npjt.update(DELETE_BY_TERMINAL, params);
        logger.info("deleted " + rows);
    }



}
